package org.openapi.vo;

import com.alibaba.fastjson2.JSONArray;
import org.openapi.consts.ExpType;
import org.openapi.utils.StrUtil;
import lombok.Data;

import java.util.*;
import java.util.stream.Collectors;

/**
 * 表查询配置
 */
@Data
public class TableQuery extends TableData{
    private String alias;
    protected List<SqlField> fields = new ArrayList<>();
    protected String group;
    protected String having;
    private String combine;
    private Map<String, String> exps = new HashMap<>();
    protected Map<String,Object> params = new HashMap<>();
    private String join;

    protected SqlPager pager;

    /**
     * 取表名
     */
    public String getName(){

        return (alias == null) ?  "`"+table+"`" : alias;
    }

    /**
     * 取表名,可选择是否带模式schema
     */
    public String getName(boolean st){
        if(!st){
            return (alias == null) ?  "`"+table+"` " : alias;
        }

        String name = "`"+table+"` ";
        if(schema != null){
            name = schema + "."+name;
        }
        if(alias != null){
            name += alias;
        }
        return name;
    }

    /**
     * 添加一个参数
     * @param dataName 参数名
     * @param val 参数值
     */
    public void addParams(String dataName, Object val){
        params.put(dataName, val);
    }


    /**
     * 设置表达式
     * @param ps Map
     */
    public void setExps(Map<String, Object> ps){
        if(ps == null || ps.isEmpty()){
            return;
        }
        for(String k:ps.keySet()) {
            exps.put(k, ps.getOrDefault(k, ExpType.EQ).toString());
        }
    }
    public void setExp(String field, String exp){
        exps.put(field, exp);
    }

    public void addData(String field, Object data, String exp){
        super.addData(field, data);
        exps.put(field, exp);
    }

    /**
     * 设置查询字段
     * @param column 列
     */
    public void setColumn(String column){
        String[] fs = column.split(",");
        for(String f:fs){
            fields.add(new SqlField(f.trim()));
        }
    }

    /**
     * 生成查询SQL
     * @return String
     */
    public String getSql(String dataScope){
        StringBuilder sql = new StringBuilder("SELECT ");
        if(fields == null || fields.isEmpty()){
            sql.append(getName(false)).append(".*");
        }else{
            sql.append(fields.stream().map(SqlField::getName).collect(Collectors.joining(",")));
        }

        sql.append(" FROM ").append(getName(true)).append("\n");
        String w ="";
        if(StrUtil.isNotEmpty(dataScope)){
            w = getAlias()+"."+dataScope+"\n";
        }
        if(!data.isEmpty()) {
            w += parseWhere(this);;
        }
        if(StrUtil.isNotEmpty(w)) {
            sql.append(" WHERE ").append(w).append("\n");
        }
        if(StrUtil.isNotEmpty(group)){
            sql.append(" GROUP BY ").append(group).append("\n");
        }
        if(StrUtil.isNotEmpty(having)){
            sql.append(" HAVING ").append(having).append("\n");
        }
        return sql.toString();

    }

    /**
     * 解析查询条件
     */

    public String parseWhere(TableQuery table){
        //软删除，默认只查询未删除数据
        if(table.getModel() != null && StrUtil.isNotEmpty(table.getModel().getDelField()) && !table.getData().containsKey(table.getModel().getDelField())){
            table.getData().put(table.getModel().getDelField(), "0");
        }

        //查询条件
        String combine = table.getCombine();
        StringBuilder sb = new StringBuilder();
        for(String fc:table.getData().keySet()){
            String dataName = StrUtil.toUnderScoreCase(fc);
            String exp = (table.getExps() == null || table.getExps().isEmpty() || !table.getExps().containsKey(fc)) ? ExpType.EQ : table.getExps().get(fc);
            if(data.containsKey(dataName)){
                dataName = table.getTable()+"_"+dataName;
            }
            String w = table.getName()+"." + fc + parseWhere(dataName, exp);

            Object v = table.getData().get(fc);
            if(!exp.equalsIgnoreCase(ExpType.IN) && !exp.equalsIgnoreCase(ExpType.NOT_IN) && !exp.equalsIgnoreCase(ExpType.BT)){
                addParams(dataName, v);
            }else {
                String[] vv;
                if(v instanceof JSONArray){
                    vv = ((JSONArray)v).stream().map(d->d.toString()).toArray(String[]::new);
                }else{
                    String vs = v.toString();
                    if(vs.startsWith("[")){
                        vs = vs.substring(1, vs.length()-1);
                    }
                    vv = vs.split(",");
                }
                if(vv == null || vv.length == 0){
                    continue;
                }
                if (exp.equalsIgnoreCase(ExpType.IN) || exp.equalsIgnoreCase(ExpType.NOT_IN)) {
                    addParams(dataName, Arrays.asList(vv));
                } else if (exp.equalsIgnoreCase(ExpType.BT) && vv.length > 0) {
                    addParams(dataName + "_begin", vv[0]);
                    addParams(dataName + "_end", vv[1]);
                }
            }

            if(StrUtil.isNotEmpty(combine) && combine.contains(fc)){
                combine = combine.replace(fc, w);
            }else{
                sb.append(w).append(" AND ");
            }
        }
        if(StrUtil.isNotEmpty(combine)){
            sb.append(combine).append(" AND ");
        }
        if(sb.length() > 4) {
            sb.setLength(sb.length()-4);
        }
        return sb.toString();
    }

    public String getField(){
        StringBuilder sb = new StringBuilder();
        for(SqlField f:fields){
            sb.append(',').append(getName()).append(".").append(f.getField()).append(" ");
            if(f.getAlias() != null){
                sb.append(f.getAlias());
            }
        }
        return sb.toString();
    }

    public String parseWhere(String dataName, String type){
        if(type.equalsIgnoreCase(ExpType.EQ)){
            return  " = :"+ dataName;
        }else if(type.equalsIgnoreCase(ExpType.NEQ)){
            return " != :"+ dataName;
        }else if(type.equalsIgnoreCase(ExpType.LT)){
            return " < :"+ dataName;
        }else if(type.equalsIgnoreCase(ExpType.LTE)){
            return " <= :"+ dataName;
        }else if(type.equalsIgnoreCase(ExpType.GT)){
            return " > :"+ dataName;
        }else if(type.equalsIgnoreCase(ExpType.GTE)){
            return " >= :"+ dataName;
        }else if(type.equalsIgnoreCase(ExpType.LK) || type.equalsIgnoreCase(ExpType.LIKE)){
            return " LIKE concat('%', :"+ dataName+", '%')";
        }else if(type.equalsIgnoreCase(ExpType.LL)){
            return " LIKE concat('%',:"+ dataName+")";
        }else if(type.equalsIgnoreCase(ExpType.RL)){
            return " LIKE concat(:"+ dataName+", '%')";
        }else if(type.equalsIgnoreCase(ExpType.IN)){
            return " IN (:"+ dataName+")";
        }else if(type.equalsIgnoreCase(ExpType.NOT_IN)){
            return " NOT IN (:"+ dataName+")";
        }else if(type.equalsIgnoreCase(ExpType.NULL)){
            return " IS NULL";
        }else if(type.equalsIgnoreCase(ExpType.NOT_NULL)){
            return " IS NOT NULL";
        }else if(type.equalsIgnoreCase(ExpType.BT)){
            return " BETWEEN :"+ dataName+"_begin AND :"+ dataName+"_end";
        }else if(type.equalsIgnoreCase(ExpType.SQL)){
            //return " != :"+ dataName; //子查询
        }else{
            System.err.println("不支持的SQL条件 " + type);
        }
        return null;
    }

}
